# -*- coding: utf-8 -*-
## 获取当前目录
#from os.path import abspath, dirname, join
#base_path = abspath(dirname(__file__))
## 添加系统路径
#import sys
#sys.path.insert(0, abspath(join(base_path, '..')))

import xlwt,xlrd
from xlwt import Style

class xlsBook(object):
    _filename = ''; _sheets = [];
    def __init__(self,filename=''):
        pass
    def _getFilename(self): return self._filename
    def _setFilename(self,filename): 
        if filename: self._filename = filename
    filename = property(_getFilename,_setFilename)
    def _getSheets(self): return self._sheets
    sheets = property(_getSheets)
    def _getSheetCount(self): return len(self._sheets)
    sheetCount = property(_getSheetCount)
    def insertSheet(self,index,sheet):
        if not sheet: return
        if index < 0: index = 0
        if len(self._sheets) < index: index = len(self._sheets)
        # check if has sheet with the same name
        has_name = False
        for _sheet in self._sheets:
            if _sheet.name == sheet.name: has_name = True; break
        if not has_name: self._sheets.insert(index, sheet)
    def appendSheet(self,sheet):
        self.insertSheet(len(self._sheets),sheet)
    def popSheet(self,index):
        if index >= len(self._sheets): return None
        return self._sheets.pop(index)
    def loadFile(self,filename):
        book = xlrd.open_workbook(filename)
        self._sheets = []
        sheet_count = book.nsheets
        for i in range(sheet_count):
            sheet = book.sheet_by_index(i)
            _sheet = xlsSheet(sheet.name,sheet)
            self._sheets.append(_sheet)
    def saveFile(self,filename=None):
        if not filename: filename = self._filename
        _book = xlwt.Workbook()
        i = 1
        for sheet in self._sheets:
            sheet.saveToBook(_book)
        _book.save(filename)
class xlsSheet(object):
    _name = ''; _cells = {}; _rows = 0; _columns = 0; 
    def __init__(self,name,fileSheet=None):
        self._name = name
        import datetime,random
        if not self._name: self._name = '%s%d' % (datetime.datetime.strftime('%Y%m%d%H%M%S'), random.randint(0,1000))
        if fileSheet: self.loadSheet(fileSheet)
    def _getName(self): return self._name
    def _setName(self): return self._name
    name = property(_getName,_setName)
    def _getRows(self): return self._rows;
    rowCount = property(_getRows)
    def _getColumns(self): return self._columns
    columnCount = property(_getColumns)
    def _getCells(self): return self._cells
    cells = property(_getCells)
    def __getitem__(self,pos_tuple):
        if type(pos_tuple) != type((1,1)): return None
        if self._cells.has_key(pos_tuple): 
            return self._cells[(pos_tuple[0],pos_tuple[1])]
        else: return None
    def __setitem__(self,pos_tuple,cellObject):
        if type(pos_tuple) != type((1,1)): return
        if type(cellObject) != type(xlsCell()): return
        if pos_tuple[0] > self._rows: self._rows = pos_tuple[0]
        if pos_tuple[1] > self._columns: self._columns = pos_tuple[1]
        self._cells[pos_tuple] = cellObject
    def addCellSimple(self,row,col,value):
        cell = xlsCell(row,col,value)
        self.addCellObject(cell)
    def addCellFull(self,row,col,value,rowSpan,colSpan,style=Style.default_style):
        cell = xlsCell(row,col,value,rowSpan,colSpan,style)
        self.addCellObject(cell)
    def addCellObject(self,cell):
        self[(cell.row,cell.col)] = cell
    def loadSheet(self,fileSheet):
        self._cells = {}
        if not fileSheet: return
        ncols = fileSheet.ncols; nrows = fileSheet.nrows
        for i in range(nrows):
            for j in range(ncols):
                _item_value = fileSheet.cell_value(i,j)
                if _item_value: self[(i,j)] = xlsCell(i,j,_item_value,1,1,'')
    def saveToBook(self,book):
        if not book: return None
        sheet = book.add_sheet(self._name)
        if not sheet: return None
        keys = self._cells.keys()
        for (row,col) in keys:
            cell = self[(row,col)]
            if not cell: continue
            if cell.rowSpan > 1 or cell.colSpan > 1: 
                sheet.write_merge(row, row+cell.rowSpan-1, col, col+cell.colSpan-1, cell.value, cell.style)
            else: sheet.write(row,col,cell.value,cell.style)
        return sheet
class xlsCell(object):
    row = 0; col = 0; rowSpan = 1; colSpan = 1; value = ''; style = Style.default_style;
    def __init__(self,row=0,col=0,value='',rowSpan=1,colSpan=1,style=Style.default_style):
        self.row = row; self.col = col; self.value = value; 
        self.rowSpan = rowSpan; self.colSpan = colSpan; self.style = style;
class xlsHelper(object):
    def sheetFromHtmlTable(self,tableString,sheetname=''):
        import re
        caption_re = re.compile('<caption.*?>(.*?)</caption>',re.I|re.M|re.S)
        tr_re = re.compile('<tr.*?>(.*?)</tr>',re.I|re.M|re.S)
        th_re = re.compile('<th.*?>.*?</th>',re.I|re.M|re.S)
        td_re = re.compile('<td.*?>.*?</td>',re.I|re.M|re.S)
        rowspan_re = re.compile('\srowspan=.*?(\d+).*?',re.I|re.M|re.S)
        colspan_re = re.compile('\scolspan=.*?(\d+).*?',re.I|re.M|re.S)
        th_content_re = re.compile('<th.*?>(.*?)</th>',re.I|re.M|re.S)
        td_content_re = re.compile('<td.*?>(.*?)</td>',re.I|re.M|re.S)
        label_re = re.compile('(<.*?>)',re.I|re.M|re.S)
        import random
        _sheetname = u''
        if sheetname: _sheetname = sheetname
        else:
            captions = caption_re.findall(tableString)
            if len(captions) > 0: _sheetname = captions[0]
        if not _sheetname: _sheetname = u'sheet_%d' % random.randint(1,1000)
        xsheet = xlsSheet(_sheetname)
        row_index = 0; col_index = 0
        trs = tr_re.findall(tableString)
        for tr in trs:
            ths = th_re.findall(tr)
            for th in ths:
                cell = xlsCell()
                cell.row = row_index; cell.col = col_index;
                rowspans = rowspan_re.findall(th)
                if len(rowspans) > 0: cell.rowSpan = int(rowspans[0])
                colspans = colspan_re.findall(th)
                if len(colspans) > 0: cell.colSpan = int(colspans[0])
                contents = th_content_re.findall(th)
                content = u''
                if len(contents) > 0: 
                    content = label_re.sub(u'',contents[0])
                cell.value = content
                from xlwt.Style import XFStyle
                from xlwt.Formatting import Font
                font = Font(); font.bold = True; style = XFStyle(); style.font = font;
                cell.style = style;
                xsheet.addCellObject(cell)
                col_index += cell.colSpan;
            tds = td_re.findall(tr)
            for td in tds:
                cell = xlsCell()
                cell.row = row_index; cell.col = col_index;
                rowspans = rowspan_re.findall(td)
                if len(rowspans) > 0: cell.rowSpan = int(rowspans[0])
                colspans = colspan_re.findall(td)
                if len(colspans) > 0: cell.colSpan = int(colspans[0])
                contents = td_content_re.findall(td)
                if len(contents) > 0: cell.value = contents[0]
                xsheet.addCellObject(cell)
                col_index += cell.colSpan;
            row_index += 1; col_index = 0;
        return xsheet
    def bookFromHtmlTable(self,filename,tableString):
        xbook = xlsBook(); xbook.filename = filename;
        sheet = self.sheetFromHtmlTable(tableString)
        xbook.appendSheet(sheet)
        return xbook
    def fileFromHtmlTable(self,filename,tableString):
        xbook = self.bookFromHtmlTable(filename, tableString)
        xbook.saveFile()
        
if __name__ == '__main__':
    fname = 'd:\Book1.xls'
    tableString = u'''<table><caption>testdb</caption>
    <tr><th>第一个th</th><th colspan="2">横跨2列的th</th><th>一般th</th></tr>
    <tr><td>第一个td吗</td><td>一般td</td><td colspan="2">横跨两列的td</td></tr>
    <tr><td colspan="2">横跨两列的td</td><td>一般td</td><td>一般td</td></tr>
    <tr><td>一般td</td><td colspan='2'>一2般td</td><td rowspan="2">横跨2行td</td></tr>
    <tr><td colspan="2">横跨两列的td</td><td>一般td</td></tr>
    </table>
    '''
    hlp = xlsHelper()
    hlp.fileFromHtmlTable(fname, tableString)
    print 'test'